<?php

namespace App\Http\Controllers\Common;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Models\Basic\Region;
use App\Models\Basic\RegionProvince;
use App\Models\Basic\Dictionary;
use App\Models\Basic\DictionaryMember;
use App\Models\Customer\Customer;
use App\Models\Customer\CustomerDepartment;
use App\Models\Customer\Doctor;
use App\Models\Product\Product;
use App\Models\Product\ProductPackage;
use Carbon\Carbon;
use DB;

class ExportController extends Controller
{
    /**
     * 把远端数据导入到本地并生成sql文件
     */
    public function exportOnline(Request $request, $tableName)
    {
        /*$connect = mysqli_connect('192.168.6.190', 'limsqpRO', 'quanpu');
        mysqli_select_db($connect, "biolims");
        $result = mysqli_query($connect, "select * from " . $tableName);*/

        $connect = mysqli_connect('localhost', 'root', '123456');
        mysqli_select_db($connect, "kati_lims");
        mysqli_set_charset($connect, 'utf8');
        $result = mysqli_query($connect, "select * from " . $tableName);
        $uid = 8;
        $insertData = [];
        $title = [];
        $code = [];
        $i = 0;
        while ($onlineData = mysqli_fetch_assoc($result)) {
            $newData = [];
            if ($tableName == 'area') {
                $newData['title'] = $onlineData['name'];
                $newData['code'] = $onlineData['id'];
                $newData['state'] = 1;
                $newData['created_by'] = $uid;
                $newData['updated_by'] = $uid;
                $newData['created_at'] = new Carbon();
                $newData['updated_at'] = new Carbon();
                $insertData[] = $newData;
            } else if ($tableName == 'province') {
                $region = Region::where('code', $onlineData['area'])->select(['id'])->first();
                if ($region) {
                    $newData['region_id'] = $region->id;
                } else {
                    $newData['region_id'] = 1;//$val[3] = SF99 不详
                }
                $newData['code'] = $onlineData['id'];
                $newData['title'] = $onlineData['name'];
                $newData['description'] = $onlineData['note'];
                $newData['charged_by'] = null;//lims值为字符串
                $newData['rank'] = null;
                $newData['state'] = 1;
                $newData['created_by'] = $uid;
                $newData['updated_by'] = $uid;
                $newData['created_at'] = new Carbon();
                $newData['updated_at'] = new Carbon();
                $insertData[] = $newData;
            } else if ($tableName == 't_dic_main_type') {
                $i++;
                if (in_array($onlineData['ID'], $code)) {
                    $newData['code'] = $onlineData['ID'] . $i;
                } else {
                    $newData['code'] = $onlineData['ID'];
                }
                if (in_array($onlineData['NAME'], $title)) {
                    $newData['title'] = $onlineData['NAME'] . $i;
                } else {
                    $newData['title'] = $onlineData['NAME'];
                }
                $code[] = $onlineData['ID'];
                $title[] = $onlineData['NAME'];
                $newData['rank'] = $onlineData['ORDER_NUMBER'];
                $newData['description'] = null;
                $newData['state'] = $onlineData['STATE'];
                $newData['created_by'] = $uid;
                $newData['updated_by'] = $uid;
                $newData['created_at'] = new Carbon();
                $newData['updated_at'] = new Carbon();
                $insertData[] = $newData;
            } else if ($tableName == 't_dic_type') {
                if ($onlineData['ID']) {
                    if (!$onlineData['parent']) {
                        $dictionary = Dictionary::where('code', $onlineData['TYPE'])->select(['id'])->first();
                        if ($dictionary) {
                            $newData['dictionary_id'] = $dictionary->id;
                        } else {
                            $newData['dictionary_id'] = 1;//字典部分type字段为空
                        }
                        $newData['code'] = $onlineData['ID'];
                        $newData['title'] = $onlineData['NAME'];
                        $newData['description'] = $onlineData['NOTE'];
                        $newData['rank'] = $onlineData['ORDER_NUMBER'];
                        if ($onlineData['STATE']) {
                            $newData['state'] = $onlineData['STATE'];
                        } else {
                            $newData['state'] = 0;
                        }
                        $newData['parent_id'] = null;
                        $newData['created_by'] = $uid;
                        $newData['updated_by'] = $uid;
                        $newData['created_at'] = new Carbon();
                        $newData['updated_at'] = new Carbon();
                        $insertData[] = $newData;
                    } else {
                        $parentIdArr[] = $onlineData;
                    }
                }
            } else if ($tableName == 'family_relation') {
                $newData['dictionary_id'] = null;
                $newData['parent_id'] = null;
                $newData['title'] = $onlineData['name'];
                $newData['code'] = $onlineData['id'];
                $newData['description'] = null;
                $newData['rank'] = $onlineData['order_number'];
                $newData['state'] = $onlineData['state'];
                $newData['created_by'] = $uid;
                $newData['updated_by'] = $uid;
                $newData['created_at'] = new Carbon();
                $newData['updated_at'] = new Carbon();
                $insertData[] = $newData;
            } else if ($tableName == 'crm_customer') {
                if (in_array($onlineData['name'], $title)) {
                    $newData['title'] = $onlineData['name'] . $i;
                } else {
                    $newData['title'] = $onlineData['name'];
                }
                if (in_array($onlineData['id'], $code)) {
                    $newData['code'] = $onlineData['id'] . $i;
                } else {
                    $newData['code'] = $onlineData['id'];
                }
                $code[] = $onlineData['id'];
                $title[] = $onlineData['name'];
                $newData['description'] = null;
                $newData['charged_by'] = null;
                $dictionaryMember = DictionaryMember::where('code', $onlineData['type'])->select(['id'])->first();
                if ($dictionaryMember) {
                    $newData['type'] = $dictionaryMember->id;
                } else {
                    $newData['type'] = 1;//有空数据
                }
                $newData['address'] = $onlineData['street'];
                $newData['postcode'] = $onlineData['postcode'];
                $newData['email'] = $onlineData['email'];
                $newData['field'] = $onlineData['yjly'];
                $newData['phone'] = null;
                $regions = Region::where('code', $onlineData['area'])->select(['id'])->first();
                if ($regions) {
                    $newData['region'] = $regions->id;
                } else {
                    $newData['region'] = 1;//存在空值
                }
                $province = RegionProvince::where('code', $onlineData['province'])->select(['id'])->first();
                if ($province) {
                    $newData['province'] = $province->id;
                } else {
                    $newData['province'] = 1;
                }
                if ($onlineData['state'] == '1cs') {
                    $newData['state'] = 1;
                } else {
                    $newData['state'] = 0;
                }
                $newData['created_by'] = $uid;
                $newData['updated_by'] = $uid;
                $newData['created_at'] = new Carbon();
                $newData['updated_at'] = new Carbon();
                $insertData[] = $newData;
            } else if ($tableName == 'crm_department') {
                if ($onlineData['id'] != '') {
                    $customer = Customer::where('code', $onlineData['crm_customer'])->select(['id'])->first();
                    if ($customer) {
                        $newData['customer_id'] = $customer->id;
                    } else {
                        return $this->error('common.data_format_error');
                    }
                    if ($onlineData['name'] == '') {
                        $newData['title'] = $onlineData['id'];//存在空值,先用编码代替
                    } else {
                        $newData['title'] = $onlineData['name'];
                    }
                    $newData['code'] = $onlineData['id'];
                    $newData['description'] = null;
                    $newData['phone'] = $onlineData['tel_number'];
                    $newData['email'] = $onlineData['email'];
                    $newData['state'] = $onlineData['state'];
                    if ($onlineData['zyly'] == '') {
                        $newData['field'] = null;
                    } else {
                        $dictionaryMember = DictionaryMember::where('code', $onlineData['zyly'])->select(['id'])->first();
                        $newData['field'] = $dictionaryMember->id;
                    }
                    $newData['salesman'] = null;//暂时先存null
                    $region = Region::where('code', $onlineData['area'])->select(['id'])->first();
                    if ($region) {
                        $newData['region'] = $region->id;
                    } else {
                        $newData['region'] = 1;//线上存在空值, 暂时先定为1
                    }
                    $province = RegionProvince::where('code', $onlineData['province'])->select(['id'])->first();
                    if ($province) {
                        $newData['province'] = $province->id;
                    } else {
                        $newData['province'] = 1;//线上存在空值, 暂时先定为1
                    }
                    $newData['rank'] = null;
                    $newData['created_by'] = $uid;
                    $newData['updated_by'] = $uid;
                    $newData['created_at'] = new Carbon();
                    $newData['updated_at'] = new Carbon();
                    $insertData[] = $newData;
                }
            } else if ($tableName == 'crm_doctor') {
                if ($onlineData['id'] != '' && $onlineData['crm_department'] != '') {
                    //crm_customer字段部分为空值 通过crm_department字段查找所属医院
                    /*$customer = Customer::where('code',$val[17])->select(['id'])->first();
                    if($customer){
                        $newData['customer_id'] = $customer->id;
                    }else{
                        return $this->error('common.data_format_error');
                    }*/
                    $department = CustomerDepartment::where('code', $onlineData['crm_department'])->select(['id', 'customer_id'])->first();
                    if ($department) {
                        $newData['department_id'] = $department->id;
                        $newData['customer_id'] = $department->customer_id;
                    } else {
                        return $this->error('common.data_format_error');
                    }
                    $newData['title'] = $onlineData['name'];
                    $newData['code'] = $onlineData['id'];
                    $newData['job_title'] = $onlineData['job_title'];
                    if ($onlineData['is_month_fee']) {
                        $newData['is_monthly'] = $onlineData['is_month_fee'];
                    } else {
                        $newData['is_monthly'] = 0;
                    }
                    $newData['salesman'] = 1;//暂时先定为1
                    if ($onlineData['mobile'] != '') {
                        $newData['phone'] = $onlineData['mobile'];
                    } else {
                        $newData['phone'] = '未知';//手机号空值较多
                    }
                    $newData['email'] = $onlineData['mail'];
                    $newData['alipay'] = $onlineData['pay_treasure'];;
                    $newData['wechat'] = $onlineData['wei_chat_id'];
                    if ($onlineData['zyly'] != '') {
                        $dictionaryMember = DictionaryMember::where('code', $onlineData['zyly'])->select(['id'])->first();
                        $newData['field'] = $dictionaryMember->id;
                    } else {
                        $newData['field'] = null;
                    }
                    if ($onlineData['type'] != '') {
                        $dictionaryMember = DictionaryMember::where('code', $onlineData['type'])->select(['id'])->first();
                        $newData['type'] = $dictionaryMember->id;
                    } else {
                        $newData['type'] = 1;//空值暂时先存为1
                    }
                    $newData['intro'] = $onlineData['doctor_introduction'];
                    $newData['specialty'] = $onlineData['clinic_characteristics'];
                    $newData['other'] = $onlineData['other_information'];
                    if ($onlineData['area'] != '') {
                        $region = Region::where('code', $onlineData['area'])->select(['id'])->first();
                        if ($region) {
                            $newData['region'] = $region->id;
                        } else {
                            continue;
                        }
                    } else {
                        $newData['region'] = 1;//空值暂时先存为1
                    }
                    if ($onlineData['province'] != '') {
                        $province = RegionProvince::where('code', $onlineData['province'])->select(['id'])->first();
                        if ($province) {
                            $newData['province'] = $province->id;
                        } else {
                            continue;
                        }
                    } else {
                        $newData['province'] = 1;//空值暂时先存为1
                    }
                    if ($onlineData['state']) {
                        $newData['state'] = $onlineData['state'];
                    } else {
                        $newData['state'] = 0;
                    }
                    $newData['created_by'] = $uid;
                    $newData['updated_by'] = $uid;
                    $newData['created_at'] = new Carbon();
                    $newData['updated_at'] = new Carbon();
                    $insertData[] = $newData;
                }
            } else if ($tableName == 'sys_product') {
                $sysProduct = Product::where('id', $onlineData['PARENT'])->select(['id'])->first();
                if ($sysProduct) {
                    $newData['parent_id'] = $sysProduct->id;
                } else {
                    $newData['parent_id'] = null;
                }
                if (in_array($onlineData['NAME'], $title)) {
                    $i++;
                    $newData['title'] = $onlineData['NAME'] . '_' . $i;
                } else {
                    $newData['title'] = $onlineData['NAME'];
                }
                if (in_array($onlineData['ID'], $title)) {
                    $i++;
                    $newData['code'] = $onlineData['ID'] . '_' . $i;
                } else {
                    $newData['code'] = $onlineData['ID'];
                }
                $code[] = $onlineData['ID'];
                $title[] = $onlineData['NAME'];
                $testMethod = DictionaryMember::where('id', $onlineData['TEST_METHOD'])->select(['id'])->first();
                if ($testMethod) {
                    $newData['test_method'] = $testMethod->id;
                } else {
                    $newData['test_method'] = null;
                }
                $newData['price'] = $onlineData['MONEY'];
                if ($onlineData['is_urgent']) {
                    $newData['is_urgent'] = 1;
                } else {
                    $newData['is_urgent'] = 0;
                }
                if ($onlineData['is_co_project']) {
                    $newData['is_package'] = 1;
                } else {
                    $newData['is_package'] = 0;
                }
                if ($onlineData['is_genetic'] == 1) {
                    $newData['is_genetic_disease'] = 1;
                } else {
                    $newData['is_genetic_disease'] = 0;
                }
                if ($onlineData['is_genetic_diagnose'] == 1) {
                    $newData['is_genetic_diagnosis'] = 1;
                } else {
                    $newData['is_genetic_diagnosis'] = 1;
                }
                if ($onlineData['is_family'] == 1) {
                    $newData['is_family'] = 1;
                } else {
                    $newData['is_family'] = 0;
                }
                if ($onlineData['is_contain_family'] == 1) {
                    $newData['is_contain_family'] = 1;
                } else {
                    $newData['is_contain_family'] = 0;
                }
                if ($onlineData['is_more_monitor'] == 1) {
                    $newData['is_more_monitor'] = 1;
                } else {
                    $newData['is_more_monitor'] = 0;
                }
                if ($onlineData['is_order']) {
                    $newData['is_alone_order'] = 1;
                } else {
                    $newData['is_alone_order'] = 0;
                }
                if ($onlineData['is_report']) {
                    $newData['is_report'] = 1;
                } else {
                    $newData['is_report'] = 0;
                }
                if ($onlineData['STATE']) {
                    $newData['state'] = 1;
                } else {
                    $newData['state'] = 0;
                }
                $newData['clinical_speciality'] = $onlineData['clinical_speciality'];
                $newData['clinical_significance'] = $onlineData['clinical_significance'];
                $newData['modeless_name'] = $onlineData['modeless_name'];
                $newData['sequencing_strategy'] = $onlineData['single_carry_code'];
                $newData['group_special_test'] = '';
                $newData['member_add_price'] = $onlineData['member_add_price'];
                $newData['analytic_strategy'] = $onlineData['analytic_strategy'];
                $newData['execute_kind'] = $onlineData['execute_kind'];
                $newData['analytic_variation_type'] = $onlineData['analytic_variation_type'];
                $newData['sample_type_desc'] = $onlineData['sample_type_desc'];
                $newData['sample_transport'] = $onlineData['sample_transport'];
                $newData['free_verification_num'] = $onlineData['free_verification_num'];
                $newData['point_variation_method'] = $onlineData['point_variation_method'];
                $newData['point_variation_price'] = $onlineData['point_variation_price'];
                $newData['cnv_variation_method'] = $onlineData['cnv_variation_method'];
                $newData['cnv_variation_price'] = $onlineData['cnv_variation_price'];
                $newData['report_period'] = $onlineData['report_period'];
                $newData['ngs_period'] = $onlineData['ngs_period'];
                $dictionaryMember = DictionaryMember::where('id', $onlineData['product_attribute'])->select(['id'])->first();
                if ($dictionaryMember) {
                    $newData['product_attribute'] = $dictionaryMember->id;
                } else {
                    $newData['product_attribute'] = null;
                }
                $newData['remark'] = $onlineData['note'];
                $newData['monitor_param'] = $onlineData['monitor_param'];
                $newData['morepcr_primer'] = $onlineData['morepcr_primer'];
                $newData['capture_chip'] = $onlineData['capture_chip'];
                $newData['sequencing_depth'] = $onlineData['sequencing_depth'];
                $newData['single_carry_code'] = $onlineData['single_carry_code'];
                $newData['single_carry_name'] = $onlineData['single_carry_name'];
                $newData['library_type'] = $onlineData['LIBRARY_TYPE'];
                $newData['method'] = '';
                $newData['lci_index'] = $onlineData['LCI_INDEX'];
                $newData['si_index'] = $onlineData['SI_INDEX'];
                $newData['insert_size'] = $onlineData['SI_INDEX'];
                $newData['target_yield'] = $onlineData['TARGET_YIELD'];
                $newData['qc_requirements'] = $onlineData['QC_REQUIREMENTS'];
                if ($onlineData['SEQUENCE_PLATFORM']) {
                    $newData['sequence_platform'] = $onlineData['SEQUENCE_PLATFORM'];
                } else {
                    $newData['sequence_platform'] = null;
                }
                if ($onlineData['SEQUENCE_TYPE']) {
                    $newData['sequence_type'] = $onlineData['SEQUENCE_TYPE'];
                } else {
                    $newData['sequence_type'] = null;
                }
                $newData['sequence_length'] = $onlineData['SEQUENCE_LENGTH'];
                $newData['client_index'] = $onlineData['CLIENT_INDEX'];
                $newData['phix'] = $onlineData['PHIX'];
                $newData['delivery_method'] = $onlineData['DELIVERY_METHOD'];
                $newData['data_analysis'] = $onlineData['DATA_ANALYSIS'];
                $newData['analysis_details'] = $onlineData['ANALYSIS_DETAILS'];
                $newData['flux'] = $onlineData['FLUX'];
                $newData['discount'] = $onlineData['DISCOUNT'];
                $newData['product_fee'] = $onlineData['PRODUCT_FEE'];
                $newData['way'] = $onlineData['WAY'];
                $newData['created_by'] = $uid;
                $newData['updated_by'] = $uid;
                $newData['data_scope'] = '';
                $newData['created_at'] = $onlineData['CREATE_DATE'];
                $newData['updated_at'] = $onlineData['modify_date'];
                $insertData[] = $newData;
            } else if ($tableName == 'packege_product') {
                $productPackege = Product::where('code', $onlineData['pac_id'])->select(['id'])->first();
                if ($productPackege) {
                    $newData['package_id'] = $productPackege->id;
                } else {
//                    continue;
                    $newData['package_id'] = 1;  //空数据暂时先存1
                }
                $product = Product::where('code', $onlineData['product_id'])->select(['id'])->first();
                if ($product) {
                    $newData['product_id'] = $product->id;
                } else {
                    dd($onlineData['product_id']);
                    return $this->error('common.data_format_error');
                }
                $newData['created_by'] = $uid;
                $newData['updated_by'] = $uid;
                $newData['data_scope'] = '';
                $newData['created_at'] = $onlineData['create_date'];
                $newData['updated_at'] = $onlineData['modify_date'];
                $insertData[] = $newData;
            }
        }

        if ($tableName == 'area') {
            Region::insert($insertData);
            $sqlPath = storage_path('sql/regions.sql');
            exec("mysqldump -uroot -p123456 lims regions --skip-lock-tables>" . $sqlPath);
        } else if ($tableName == 'province') {
            RegionProvince::insert($insertData);
            $sqlPath = storage_path('sql/provinces.sql');
            exec("mysqldump -uroot -p123456 lims provinces --skip-lock-tables>" . $sqlPath);
        } else if ($tableName == 't_dic_main_type') {
            Dictionary::insert($insertData);
            $sqlPath = storage_path('sql/dictionaries.sql');
            exec("mysqldump -uroot -p123456 lims dictionaries --skip-lock-tables>" . $sqlPath);
        } else if ($tableName == 't_dic_type') {
            DictionaryMember::insert($insertData);
            if (count($parentIdArr) > 0) {
                $insertData = [];
                foreach ($parentIdArr as $k => $v) {
                    $newData = [];
                    $dictionary = Dictionary::where('code', $v['TYPE'])->select(['id'])->first();
                    if ($dictionary) {
                        $newData['dictionary_id'] = $dictionary->id;
                    } else {
                        $newData['dictionary_id'] = 1;//字典部分type字段为空
                    }
                    $dictionaryMember = DictionaryMember::where('code', $v['parent'])->select(['id'])->first();
                    $newData['code'] = $v['ID'];
                    $newData['title'] = $v['NAME'];
                    $newData['description'] = $v['NOTE'];
                    $newData['rank'] = $v['ORDER_NUMBER'];
                    if ($v['STATE']) {
                        $newData['state'] = $v['STATE'];
                    } else {
                        $newData['state'] = 0;
                    }
                    $newData['parent_id'] = $dictionaryMember->id;
                    $newData['created_by'] = $uid;
                    $newData['updated_by'] = $uid;
                    $newData['created_at'] = new Carbon();
                    $newData['updated_at'] = new Carbon();
                    $insertData[] = $newData;
                }
                DictionaryMember::insert($insertData);
            }
            $sqlPath = storage_path('sql/dictionary_members.sql');
            exec("mysqldump -uroot -p123456 lims dictionary_members --skip-lock-tables>" . $sqlPath);
        } else if ($tableName == 'family_relation') {
            $data = ['title' => '家系', 'code' => 'jx', 'description' => null, 'rank' => 200, 'state' => 1, 'created_by' => $uid, 'updated_by' => $uid];
            $model = new Dictionary();
            $dictionary = $model->create($data);
            $sqlPath = storage_path('sql/dictionaries.sql');
            exec("mysqldump -uroot -p123456 lims dictionaries --skip-lock-tables>" . $sqlPath);

            foreach ($insertData as $key => $val) {
                $insertData[$key]['dictionary_id'] = $dictionary->id;
            }
            DictionaryMember::insert($insertData);
            $sqlPath = storage_path('sql/dictionary_members.sql');
            exec("mysqldump -uroot -p123456 lims dictionary_members --skip-lock-tables>" . $sqlPath);
        } else if ($tableName == 'crm_customer') {
            Customer::insert($insertData);
            $sqlPath = storage_path('sql/customers.sql');
            exec("mysqldump -uroot -p123456 lims customers --skip-lock-tables>" . $sqlPath);
        } else if ($tableName == 'crm_department') {
            CustomerDepartment::insert($insertData);
            $sqlPath = storage_path('sql/customer_departments.sql');
            exec("mysqldump -uroot -p123456 lims customer_departments --skip-lock-tables>" . $sqlPath);
        } else if ($tableName == 'crm_doctor') {
            $num = count($insertData);
            $limit = 1000;
            $length = ceil($num / $limit);
            for ($i = 0; $i < $length; $i++) {
                $data = array_slice($insertData, $i * $limit, $limit);
                Doctor::insert($data);
                sleep(1);//批量插入,减轻数据库压力
            }
            $sqlPath = storage_path('sql/customer_doctors.sql');
            exec("mysqldump -uroot -p123456 lims customer_doctors --skip-lock-tables>" . $sqlPath);
        } else if ($tableName == 'sys_product') {
            $num = count($insertData);
            $limit = 1000;
            $length = ceil($num / $limit);
            for ($i = 0; $i < $length; $i++) {
                $data = array_slice($insertData, $i * $limit, $limit);
                DB::delete('delete from products');
                Product::insert($data);
                $sqlPath = storage_path('sql/products' . '_' . $i . '.sql');
                exec("mysqldump -uroot -p123456 lims products --skip-lock-tables>" . $sqlPath);
                sleep(1);//批量插入,减轻数据库压力
            }
        } else if ($tableName == 'packege_product') {
            ProductPackage::insert($insertData);
            $sqlPath = storage_path('sql/product_packages.sql');
            exec("mysqldump -uroot -p123456 lims product_packages --skip-lock-tables>" . $sqlPath);
        }

        echo('success !');
        die;


        if (count($onlineData) > 0) {
            $insertData = [];
//            $uid = $request->user()->id;
            $uid = 8;
            if ($tableName == 'area') {
                foreach ($onlineData as $key => $val) {
                    $newData = [];
                    $newData['title'] = $val[1];
                    $newData['code'] = $val[0];
                    $newData['state'] = 1;
                    $newData['created_by'] = $uid;
                    $newData['updated_by'] = $uid;
                    $newData['created_at'] = new Carbon();
                    $newData['updated_at'] = new Carbon();
                    $insertData[] = $newData;
                }
                Region::insert($insertData);
                $sqlPath = storage_path('sql/regions.sql');
                exec("mysqldump -uroot -p123456 lims regions --skip-lock-tables>" . $sqlPath);
            } else if ($tableName == 'province') {
                foreach ($onlineData as $key => $val) {
                    $newData = [];
                    $region = Region::where('code', $val[3])->select(['id'])->first();
                    if ($region) {
                        $newData['region_id'] = $region['id'];
                    } else {
                        $newData['region_id'] = 1;//$val[3] = SF99 不详
                    }
                    $newData['code'] = $val[0];
                    $newData['title'] = $val[1];
                    $newData['description'] = $val[2];
                    $newData['charged_by'] = null;//lims值为字符串
                    $newData['rank'] = null;
                    $newData['state'] = 1;
                    $newData['created_by'] = $uid;
                    $newData['updated_by'] = $uid;
                    $newData['created_at'] = new Carbon();
                    $newData['updated_at'] = new Carbon();
                    $insertData[] = $newData;
                }
                RegionProvince::insert($insertData);
                $sqlPath = storage_path('sql/provinces.sql');
                exec("mysqldump -uroot -p123456 lims provinces --skip-lock-tables>" . $sqlPath);
            } else if ($tableName == 't_dic_type') {
                $parentIdArr = [];
                foreach ($onlineData as $key => $val) {
                    $newData = [];
                    if (!$val[10]) {
                        $dictionary = Dictionary::where('code', $val['7'])->select(['id'])->first();
                        if ($dictionary) {
                            $newData['dictionary_id'] = $dictionary['id'];
                        } else {
                            $newData['dictionary_id'] = 1;//字典部分type字段为空
                        }
                        $newData['code'] = $val[0];
                        $newData['title'] = $val[2];
                        $newData['description'] = $val[3];
                        $newData['rank'] = $val[4];
                        if ($val[5]) {
                            $newData['state'] = $val[5];
                        } else {
                            $newData['state'] = 0;
                        }
                        $newData['parent_id'] = null;
                        $newData['created_by'] = $uid;
                        $newData['updated_by'] = $uid;
                        $newData['created_at'] = new Carbon();
                        $newData['updated_at'] = new Carbon();
                        $insertData[] = $newData;
                    } else {
                        $parentIdArr[] = $val;
                    }
                }
                DictionaryMember::insert($insertData);
                if (count($parentIdArr) > 0) {
                    $insertData = [];
                    foreach ($parentIdArr as $k => $v) {
                        $newData = [];
                        $dictionary = Dictionary::where('code', $v['7'])->select(['id'])->first();
                        if ($dictionary) {
                            $newData['dictionary_id'] = $dictionary['id'];
                        } else {
                            $newData['dictionary_id'] = 1;//字典部分type字段为空
                        }
                        $dictionaryMember = DictionaryMember::where('code', $v[10])->select(['id'])->first();
                        $newData['code'] = $v[0];
                        $newData['title'] = $v[2];
                        $newData['description'] = $v[3];
                        $newData['rank'] = $v[4];
                        if ($v[5]) {
                            $newData['state'] = $v[5];
                        } else {
                            $newData['state'] = 0;
                        }
                        $newData['parent_id'] = $dictionaryMember->id;
                        $newData['created_by'] = $uid;
                        $newData['updated_by'] = $uid;
                        $newData['created_at'] = new Carbon();
                        $newData['updated_at'] = new Carbon();
                        $insertData[] = $newData;
                    }
                    DictionaryMember::insert($insertData);
                }
                $sqlPath = storage_path('sql/dictionary_members.sql');
                exec("mysqldump -uroot -p123456 lims dictionary_members --skip-lock-tables>" . $sqlPath);
            } else if ($tableName == 't_dic_main_type') {
                $title = [];
                $code = [];
                $i = 1;
                foreach ($onlineData as $key => $val) {
                    $i++;
                    $newData = [];
                    if (in_array($val[0], $code)) {
                        $newData['code'] = $val[0] . $i;
                    } else {
                        $newData['code'] = $val[0];
                    }
                    if (in_array($val[1], $title)) {
                        $newData['title'] = $val[1] . $i;
                    } else {
                        $newData['title'] = $val[1];
                    }
                    $code[] = $val[0];
                    $title[] = $val[1];
                    $newData['rank'] = $val[2];
                    $newData['description'] = null;
                    $newData['state'] = $val[3];
                    $newData['created_by'] = $uid;
                    $newData['updated_by'] = $uid;
                    $newData['created_at'] = new Carbon();
                    $newData['updated_at'] = new Carbon();
                    $insertData[] = $newData;
                }
                Dictionary::insert($insertData);
                $sqlPath = storage_path('sql/dictionaries.sql');
                exec("mysqldump -uroot -p123456 lims dictionaries --skip-lock-tables>" . $sqlPath);
            } else if ($tableName == 'family_relation') {
                $data = ['title' => '家系', 'code' => 'jx', 'description' => null, 'rank' => 200, 'state' => 1, 'created_by' => $uid, 'updated_by' => $uid];
                $model = new Dictionary();
                $dictionary = $model->create($data);
                $sqlPath = storage_path('sql/dictionaries.sql');
                exec("mysqldump -uroot -p123456 lims dictionaries --skip-lock-tables>" . $sqlPath);
                foreach ($onlineData as $key => $val) {
                    $newData = [];
                    $newData['dictionary_id'] = $dictionary->id;
                    $newData['parent_id'] = null;
                    $newData['title'] = $val[4];
                    $newData['code'] = $val[0];
                    $newData['description'] = null;
                    $newData['rank'] = $val[8];
                    $newData['state'] = $val[5];
                    $newData['created_by'] = $uid;
                    $newData['updated_by'] = $uid;
                    $newData['created_at'] = new Carbon();
                    $newData['updated_at'] = new Carbon();
                    $insertData[] = $newData;
                }
                DictionaryMember::insert($insertData);
                $sqlPath = storage_path('sql/dictionary_members.sql');
                exec("mysqldump -uroot -p123456 lims dictionary_members --skip-lock-tables>" . $sqlPath);
            } else if ($tableName == 'crm_customer') {
                $title = [];
                $code = [];
                $i = 1;
                foreach ($onlineData as $key => $val) {
                    $i++;
                    $newData = [];
                    if (in_array($val[14], $title)) {
                        $newData['title'] = $val[14] . $i;
                    } else {
                        $newData['title'] = $val[14];
                    }
                    if (in_array($val[0], $code)) {
                        $newData['code'] = $val[0] . $i;
                    } else {
                        $newData['code'] = $val[0];
                    }
                    $code[] = $val[0];
                    $title[] = $val[14];
                    $newData['description'] = null;
                    $newData['charged_by'] = null;
                    $dictionaryMember = DictionaryMember::where('code', $val[31])->select(['id'])->first();
                    if ($dictionaryMember) {
                        $newData['type'] = $dictionaryMember->id;
                    } else {
                        $newData['type'] = 1;//有空数据
                    }
                    $newData['address'] = $val[18];
                    $newData['postcode'] = $val[16];
                    $newData['email'] = $val[9];
                    $newData['field'] = $val[19];
                    $newData['phone'] = null;
                    $regions = Region::where('code', $val[33])->select(['id'])->first();
                    if ($regions) {
                        $newData['region'] = $regions->id;
                    } else {
                        $newData['region'] = 1;//存在空值
                    }
                    $province = RegionProvince::where('code', $val[34])->select(['id'])->first();
                    if ($province) {
                        $newData['province'] = $province->id;
                    } else {
                        $newData['province'] = 1;
                    }
                    if ($val[30] == '1cs') {
                        $newData['state'] = 1;
                    } else {
                        $newData['state'] = 0;
                    }
                    $newData['created_by'] = $uid;
                    $newData['updated_by'] = $uid;
                    $newData['created_at'] = new Carbon();
                    $newData['updated_at'] = new Carbon();
                    $insertData[] = $newData;
                }
                Customer::insert($insertData);
                $sqlPath = storage_path('sql/customers.sql');
                exec("mysqldump -uroot -p123456 lims customers --skip-lock-tables>" . $sqlPath);
            } else if ($tableName == 'crm_department') {
                foreach ($onlineData as $key => $val) {
                    $newData = [];
                    if ($val[0] != '') {
                        $cumtomer = Customer::where('code', $val[3])->select(['id'])->first();
                        if ($cumtomer) {
                            $newData['customer_id'] = $cumtomer->id;
                        } else {
                            return $this->error('common.data_format_error');
                        }
                        if ($val[1] == '') {
                            $newData['title'] = $val[0];//存在空值
                        } else {
                            $newData['title'] = $val[1];
                        }
                        $newData['code'] = $val[0];
                        $newData['description'] = null;
                        $newData['phone'] = $val[2];
                        $newData['email'] = $val[4];
                        $newData['state'] = $val[5];
                        if ($val[6] == '') {
                            $newData['field'] = null;
                        } else {
                            $dictionaryMember = DictionaryMember::where('code', $val[6])->select(['id'])->first();
                            $newData['field'] = $dictionaryMember->id;
                        }
                        $newData['salesman'] = null;//暂时先存null
                        $region = Region::where('code', $val[8])->select(['id'])->first();
                        if ($region) {
                            $newData['region'] = $region->id;
                        } else {
                            $newData['region'] = 1;//线上存在空值, 暂时先定为1
                        }
                        $province = RegionProvince::where('code', $val[9])->select(['id'])->first();
                        if ($province) {
                            $newData['province'] = $province->id;
                        } else {
                            $newData['province'] = 1;//线上存在空值, 暂时先定为1
                        }
                        $newData['rank'] = null;
                        $newData['created_by'] = $uid;
                        $newData['updated_by'] = $uid;
                        $newData['created_at'] = new Carbon();
                        $newData['updated_at'] = new Carbon();
                        $insertData[] = $newData;
                    }
                }
                CustomerDepartment::insert($insertData);
                $sqlPath = storage_path('sql/customer_departments.sql');
                exec("mysqldump -uroot -p123456 lims customer_departments --skip-lock-tables>" . $sqlPath);
            } else if ($tableName == 'crm_doctor') {
                foreach ($onlineData as $key => $val) {
                    $newData = [];
                    if ($val[0] != '' && $val[20] != '') {
                        //crm_customer字段部分为空值 通过crm_department字段查找所属医院
                        /*$customer = Customer::where('code',$val[17])->select(['id'])->first();
                        if($customer){
                            $newData['customer_id'] = $customer->id;
                        }else{
                            return $this->error('common.data_format_error');
                        }*/
                        $department = CustomerDepartment::where('code', $val[20])->select(['id', 'customer_id'])->first();
                        if ($department) {
                            $newData['department_id'] = $department->id;
                            $newData['customer_id'] = $department->customer_id;
                        } else {
                            return $this->error('common.data_format_error');
                        }
                        $newData['title'] = $val[11];
                        $newData['code'] = $val[0];
                        $newData['job_title'] = $val[8];
                        if ($val[7]) {
                            $newData['is_monthly'] = $val[7];
                        } else {
                            $newData['is_monthly'] = 0;
                        }
                        $newData['salesman'] = 1;//暂时先定为1
                        if ($val[10] != '') {
                            $newData['phone'] = $val[10];
                        } else {
                            $newData['phone'] = '未知';//手机号空值较多
                        }
                        $newData['email'] = $val[9];
                        $newData['alipay'] = $val[13];;
                        $newData['wechat'] = $val[15];
                        if ($val[24] != '') {
                            $dictionaryMember = DictionaryMember::where('code', $val[24])->select(['id'])->first();
                            $newData['field'] = $dictionaryMember->id;
                        } else {
                            $newData['field'] = null;
                        }
                        if ($val[19] != '') {
                            $dictionaryMember = DictionaryMember::where('code', $val[19])->select(['id'])->first();
                            $newData['type'] = $dictionaryMember->id;
                        } else {
                            $newData['type'] = 1;//空值暂时先存为1
                        }
                        $newData['intro'] = $val[4];
                        $newData['specialty'] = $val[2];
                        $newData['other'] = $val[12];
                        if ($val[22] != '') {
                            $region = Region::where('code', $val[22])->select(['id'])->first();
                            if ($region) {
                                $newData['region'] = $region->id;
                            } else {
                                continue;
                            }
                        } else {
                            $newData['region'] = 1;//空值暂时先存为1
                        }
                        if ($val[23] != '') {
                            $province = RegionProvince::where('code', $val[23])->select(['id'])->first();
                            if ($province) {
                                $newData['province'] = $province->id;
                            } else {
                                continue;
                            }
                        } else {
                            $newData['province'] = 1;//空值暂时先存为1
                        }
                        if ($val[14]) {
                            $newData['state'] = $val[14];
                        } else {
                            $newData['state'] = 0;
                        }
                        $newData['created_by'] = $uid;
                        $newData['updated_by'] = $uid;
                        $newData['created_at'] = new Carbon();
                        $newData['updated_at'] = new Carbon();
                        $insertData[] = $newData;
                    }
                }
                $num = count($insertData);
                $limit = 1000;
                $length = ceil($num / $limit);
                for ($i = 0; $i < $length; $i++) {
                    $data = array_slice($insertData, $i * $limit, $limit);
                    Doctor::insert($data);
                    sleep(1);//批量插入,减轻数据库压力
                }
                $sqlPath = storage_path('sql/customer_doctors.sql');
                exec("mysqldump -uroot -p123456 lims customer_doctors --skip-lock-tables>" . $sqlPath);
            } else if ($tableName == 'sys_product') {
                foreach ($onlineData as $key => $val) {
//                    dd($val);
                    $newData = [];
                    $sysProduct = SysProduct::where('id', $val[11])->select(['id'])->first();
                    if ($sysProduct) {
                        $newData['parent_id'] = $sysProduct->id;
                    } else {
                        $newData['parent_id'] = null;
                    }
                    $newData['title'] = $val[4];
                    $newData['code'] = $val[1];
                    $testMethod = DictionaryMember::where('id')->select(['id'])->first();
                    if ($testMethod) {
                        $newData['test_method'] = $testMethod->id;
                    } else {
                        $newData['test_method'] = null;
                    }
                    $newData['price'] = '';
                    $newData['is_urgent'] = $val[81];
                    $newData['is_package'] = $val[57];
                    if ($val[60] == 1) {
                        $newData['is_genetic_disease'] = $val[60];
                    } else {
                        $newData['is_genetic_disease'] = 0;
                    }
                    if ($val[61] == 1) {
                        $newData['is_genetic_diagnosis'] = $val[61];
                    } else {
                        $newData['is_genetic_diagnosis'] = 1;
                    }
                    if ($val[58] == 1) {
                        $newData['is_family'] = $val[58];
                    } else {
                        $newData['is_family'] = 0;
                    }
                    if ($val[59] == 1) {
                        $newData['is_contain_family'] = $val[59];
                    } else {
                        $newData['is_contain_family'] = 0;
                    }
                    if ($val[62] == 1) {
                        $newData['is_more_monitor'] = $val[62];
                    } else {
                        $newData['is_more_monitor'] = 0;
                    }
                    $newData['is_alone_order'] = $val[79];
                    $newData['is_report'] = $val[80];
                    $newData['state'] = $val[7];
                    $newData['clinical_speciality'] = '';
                    $newData['clinical_significance'] = '';
                    $newData['modeless_name'] = '';
                    $newData['sequencing_strategy'] = '';
                    $newData['group_special_test'] = '';
                    $newData['member_add_price'] = $val[63];
                    $newData['analytic_strategy'] = '';
                    $newData['execute_kind'] = '';
                    $newData['analytic_variation_type'] = '';
                    $newData['sample_type_desc'] = '';
                    $newData['sample_transport'] = '';
                    $newData['free_verification_num'] = '';
                    $newData['point_variation_method'] = '';
                    $newData['point_variation_price'] = '';
                    $newData['cnv_variation_method'] = '';
                    $newData['cnv_variation_price'] = '';
                    $newData['report_period'] = '';
                    $newData['ngs_period'] = '';
                    $dictionaryMember = DictionaryMember::where('id', $val[82])->select(['id'])->first();
                    if ($dictionaryMember) {
                        $newData['product_attribute'] = $dictionaryMember->id;
                    } else {
                        $newData['product_attribute'] = null;
                    }
                    $newData['remark'] = $val[0];
                    $newData['monitor_param'] = '';
                    $newData['morepcr_primer'] = '';
                    $newData['capture_chip'] = '';
                    $newData['sequencing_depth'] = '';
                    $newData['single_carry_code'] = '';
                    $newData['single_carry_name'] = '';
                    $newData['library_type'] = '';
                    $newData['method'] = '';
                    $newData['lci_index'] = '';
                    $newData['si_index'] = '';
                    $newData['insert_size'] = '';
                    $newData['target_yield'] = '';
                    $newData['qc_requirements'] = '';
                    $newData['sequence_platform'] = '';
                    $newData['sequence_type'] = '';
                    $newData['sequence_length'] = '';
                    $newData['client_index'] = '';
                    $newData['phix'] = '';
                    $newData['delivery_method'] = '';
                    $newData['data_analysis'] = '';
                    $newData['analysis_details'] = '';
                    $newData['flux'] = '';
                    $newData['discount'] = '';
                    $newData['product_fee'] = '';
                    $newData['way'] = '';
                    $newData['created_by'] = $uid;
                    $newData['updated_by'] = $uid;
                    $newData['data_scope'] = '';
                    $newData['created_at'] = $val[2];
                    $newData['updated_at'] = $val[78];
                    $insertData[] = $newData;
                }
            } else {
                return $this->error('common.data_format_error');
            }
        }
    }

    /**
     * 导入sql文件
     */
    public function exportSql(Request $request)
    {
        $sqlFileArr = [
            'regions',
            'provinces',
            'dictionary_members',
            'dictionaries',
            'customers',
            'customer_departments',
            'customer_doctors',
            'products_0',
            'products_1',
//            'products_2',
            'product_packages',
        ];
        foreach ($sqlFileArr as $key => $val) {
            if (in_array($val, $sqlFileArr)) {
                $fileName = storage_path() . '/sql/' . $val . '.sql';
            } else {
                return $this->error('common.data_format_error');
            }

            $sqlStr = file_get_contents($fileName);
            $str = strstr($sqlStr, "INSERT");
            $sql = strstr($str, ";", true);
//            DB::delete('delete from ' . $val);
            DB::insert($sql);
            sleep(1);
        }

        echo('success !');
        die;

//        exec("mysql -h localhost -uroot -p123456 lims dictionary_members<".$fileName);die;
//        exec("mysql -h localhost -uroot -p123456 lims -e source". $fileName);
    }
}
